﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using SocketClient;
using TheHeGeoDao;
using TheHeGeoDataProcess;
using TheHeGeoServer.Util;

namespace TheHeGeoServer.Dao
{
    public class EquipmentDao
    {
        static string connectionString = ConfigurationManager.ConnectionStrings["TheHeGeoConnection"].ToString();
        public static DataTable SelectThietBi(string maTbi)
        {
            string commandText = "Select * from [dbo].[ThietBi] where  MaThietBi = @maThietBi and Status=0";
            SqlDataReader reader;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@maThietBi", SqlDbType.VarChar);
                command.Parameters["@maThietBi"].Value = maTbi;
                try
                {
                    connection.Open();
                    DataTable dt = new DataTable();
                    reader = command.ExecuteReader();
                    dt.Load(reader);
                    return dt;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }
        public static int ShouldSendCmd(string maTB)
        {
            string commandText = "Update [dbo].[ThietBi] set [Cmd] = '' where  [MaThietBi] = @maThietBi and [Status] = '0'";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@maThietBi", SqlDbType.VarChar);
                command.Parameters["@maThietBi"].Value = maTB;
                try
                {
                    connection.Open();
                    Int32 rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
        }
        public static int SetLastUpdate(string maTB)
        {
            string commandText =
                "Update [dbo].[ThietBi] set [LastUpdate] = @lastUpdate where  [MaThietBi] = @maThietBi and [Status] = '0'";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@maThietBi", SqlDbType.VarChar);
                command.Parameters["@maThietBi"].Value = maTB;
                command.Parameters.Add("@lastUpdate", SqlDbType.DateTime);
                command.Parameters["@lastUpdate"].Value = DateTime.Now;


                try
                {
                    connection.Open();
                    Int32 rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
        }
        public static int SetNeedUpdate(string maTB)
        {
            string commandText = "Update [dbo].[ThietBi] set NeedUpdate=@needUpdate where  [MaThietBi] = @maThietBi and [Status] = '0'";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@maThietBi", SqlDbType.VarChar);
                command.Parameters["@maThietBi"].Value = maTB;
                command.Parameters.Add("@needUpdate", SqlDbType.Bit);
                command.Parameters["@needUpdate"].Value = 0;
                
                try
                {
                    connection.Open();
                    Int32 rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
        }
        public static int InsertSensorValuesHistory(Int32? maDA, string maSensor,Double value, DateTime receiveLog )
        {
            string commandText = "INSERT INTO[dbo].[SensorValuesHistory]( [MaSensor],[MaDuAn], [GiaTri],[ThoiGianNhan],[ThoiGianDo])" + 
                                "VALUES(@maSensor,@maDuAn,@giaTri,@thoiGianNhan,@thoiGianDo)";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);
                command.Parameters.Add("@maDuAn", SqlDbType.Int);
                command.Parameters["@maDuAn"].Value = maDA;
                command.Parameters.Add("@maSensor", SqlDbType.VarChar);
                command.Parameters["@maSensor"].Value = maSensor;
                command.Parameters.Add("@giaTri", SqlDbType.Float);
                command.Parameters["@giaTri"].Value = value;
                command.Parameters.Add("@thoiGianNhan", SqlDbType.DateTime);
                command.Parameters["@thoiGianNhan"].Value = DateTime.Now;
                command.Parameters.Add("@thoiGianDo", SqlDbType.DateTime);
                command.Parameters["@thoiGianDo"].Value = receiveLog;

                try
                {
                    connection.Open();
                    Int32 rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;
                }
                catch (Exception ex)
                {
                    return - 1;
                }
            }
        }
        public static int InsertSensorValuesTest(Int32? maDA, string maSensor, Double value, DateTime receiveLog)
        {


            string commandText = "INSERT INTO[dbo].[SensorValuesTest]( [MaSensor],[MaDuAn], [GiaTri],[ThoiGianNhan],[ThoiGianDo])" +
                                "VALUES(@maSensor,@maDuAn,@giaTri,@thoiGianNhan,@thoiGianDo)";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);
                command.Parameters.Add("@maDuAn", SqlDbType.Int);
                command.Parameters["@maDuAn"].Value = maDA;
                command.Parameters.Add("@maSensor", SqlDbType.VarChar);
                command.Parameters["@maSensor"].Value = maSensor;
                command.Parameters.Add("@giaTri", SqlDbType.Float);
                command.Parameters["@giaTri"].Value = value;
                command.Parameters.Add("@thoiGianNhan", SqlDbType.DateTime);
                command.Parameters["@thoiGianNhan"].Value = DateTime.Now;
                command.Parameters.Add("@thoiGianDo", SqlDbType.DateTime);
                command.Parameters["@thoiGianDo"].Value = receiveLog;

                try
                {
                    connection.Open();
                    Int32 rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
        }
        public static int InsertSensorValues1000(Int32? maDA, string maSensor, Double value, DateTime receiveLog)
        {


            string commandText = "INSERT INTO [dbo].[SensorValues1000]( [MaSensor],[MaDuAn], [GiaTri],[ThoiGianNhan],[ThoiGianDo])" +
                                "VALUES(@maSensor,@maDuAn,@giaTri,@thoiGianNhan,@thoiGianDo)";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);
                command.Parameters.Add("@maDuAn", SqlDbType.Int);
                command.Parameters["@maDuAn"].Value = maDA;
                command.Parameters.Add("@maSensor", SqlDbType.VarChar);
                command.Parameters["@maSensor"].Value = maSensor;
                command.Parameters.Add("@giaTri", SqlDbType.Float);
                command.Parameters["@giaTri"].Value = value;
                command.Parameters.Add("@thoiGianNhan", SqlDbType.DateTime);
                command.Parameters["@thoiGianNhan"].Value = DateTime.Now;
                command.Parameters.Add("@thoiGianDo", SqlDbType.DateTime);
                command.Parameters["@thoiGianDo"].Value = receiveLog;

                try
                {
                    connection.Open();
                    Int32 rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
        }
        public static int UpdateSensorValues1000(Int32? maDA ,string maSensor, Double value, DateTime receiveLog)
        {
            string getMaSensor = "select Id from [dbo].[SensorValues1000] where [MaSensor]=@maSensor order by [ThoiGianDo] asc";
            DataTable dt = new DataTable();
            SqlDataReader reader;
            using (SqlConnection connection1 = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(getMaSensor, connection1);
               
                command.Parameters.Add("@maSensor", SqlDbType.VarChar);
                command.Parameters["@maSensor"].Value = maSensor;

                try
                {
                    connection1.Open();
                    reader = command.ExecuteReader();
                    dt.Load(reader);
                    if (dt.Rows.Count < 1)
                        return -1;
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
            string commandText = "Update [dbo].[SensorValues1000] set [MaDuAn] = @maDA, [GiaTri] = @giaTri," +
                                 "[ThoiGianNhan] = @thoiGianNhan,[ThoiGianDo]=@thoiGianDo " +
                                 "where  [Id]=@Id";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);
                command.Parameters.Add("@Id", SqlDbType.Int);
                command.Parameters["@Id"].Value = Convert.ToInt32(dt.Rows[0]["Id"].ToString());

                 command.Parameters.Add("@maDA", SqlDbType.Int);
                command.Parameters["@maDA"].Value = maDA;

                //command.Parameters.Add("@maSensor", SqlDbType.VarChar);
                //command.Parameters["@maSensor"].Value = maSensor;

                command.Parameters.Add("@giaTri", SqlDbType.Float);
                command.Parameters["@giaTri"].Value = value;

                command.Parameters.Add("@thoiGianNhan", SqlDbType.DateTime);
                command.Parameters["@thoiGianNhan"].Value = DateTime.Now;

                command.Parameters.Add("@thoiGianDo", SqlDbType.DateTime);
                command.Parameters["@thoiGianDo"].Value = receiveLog;

                try
                {
                    connection.Open();
                    Int32 rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
        }

        public static int AddSensorValues1000(Int32? maDA, string maSensor, Double value, DateTime receiveLog)
        {
            string commandText =
                 "SELECT COUNT(*) FROM SensorValues1000 where [MaSensor]=@maSensor";
            Int32 count = 0;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@maSensor", SqlDbType.VarChar);
                command.Parameters["@maSensor"].Value = maSensor;
                try
                {
                    connection.Open();
                    count = (Int32)command.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
            if (count < 1000)
            {
    
              return InsertSensorValues1000(maDA, maSensor, value, receiveLog);
           
            }
            else
            {
            
                return UpdateSensorValues1000(maDA,maSensor, value, receiveLog);
            }
        }
        public static bool CheckSensorLatestValue(string maSensor)
        {
            string commandText = "Select * from [dbo].[SensorLatestValue] where  MaSensor = @maSensor";
            SqlDataReader reader;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@maSensor", SqlDbType.VarChar);
                command.Parameters["@maSensor"].Value = maSensor;
                try
                {
                    connection.Open();
                    reader = command.ExecuteReader();

                    return reader.HasRows;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }

        }
        public static int InsertSensorLatestValue(int? maDuan, string maSensor, Double value, DateTime receiveLog)
        {


            string commandText = "INSERT INTO[dbo].[SensorLatestValue]([MaDuan], [MaSensor], [GiaTri],[ThoiGianDo],[ThoiGianNhan])" +
                                "VALUES(@maDuan,@maSensor,@giaTri,@thoiGianDo,@thoiGianNhan)";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@maDuan", SqlDbType.Int);
                command.Parameters["@maDuan"].Value = maDuan;
                command.Parameters.Add("@maSensor", SqlDbType.VarChar);
                command.Parameters["@maSensor"].Value = maSensor;
                command.Parameters.Add("@giaTri", SqlDbType.Float);
                command.Parameters["@giaTri"].Value = value;
                command.Parameters.Add("@thoiGianDo", SqlDbType.DateTime);
                command.Parameters["@thoiGianDo"].Value = receiveLog;
                command.Parameters.Add("@thoiGianNhan", SqlDbType.DateTime);
                command.Parameters["@thoiGianNhan"].Value = DateTime.Now;

                try
                {
                    connection.Open();
                    Int32 rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
        }
        public static int UpdateSensorLatestValue(int? maDuan, string maSensor, Double value, DateTime receiveLog)
        {
            string commandText = "Update [dbo].[SensorLatestValue] set [MaDuan] = @maDuan, [ThoiGianDo] = @thoiGianDo,[ThoiGianNhan]=@thoiGianNhan,[GiaTri] = @giaTri where  [MaSensor] = @maSensor";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@maDuan", SqlDbType.Int);
                command.Parameters["@maDuan"].Value = maDuan;
                command.Parameters.Add("@maSensor", SqlDbType.VarChar);
                command.Parameters["@maSensor"].Value = maSensor;
                command.Parameters.Add("@giaTri", SqlDbType.Float);
                command.Parameters["@giaTri"].Value = value;
                command.Parameters.Add("@thoiGianDo", SqlDbType.DateTime);
                command.Parameters["@thoiGianDo"].Value = receiveLog;
                command.Parameters.Add("@thoiGianNhan", SqlDbType.DateTime);
                command.Parameters["@thoiGianNhan"].Value = DateTime.Now;
                try
                {
                    connection.Open();
                    Int32 rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
        }

        public static int InsertRawData(string maThietBi, string Raws, string error, string errorCode)
        {
            string commandText = "INSERT INTO[dbo].[SensorRawData]( [MaThietBi],[Raw], [Error],[ErrorCode],[ThoiGianNhan])" +
                               "VALUES(@MaThietBi,@Raw,@Error,@ErrorCode,@ThoiGianNhan)";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@MaThietBi", SqlDbType.VarChar);
                command.Parameters["@MaThietBi"].Value = maThietBi;
                command.Parameters.Add("@Raw", SqlDbType.NVarChar);
                command.Parameters["@Raw"].Value = Raws;
                command.Parameters.Add("@Error", SqlDbType.VarChar);
                command.Parameters["@Error"].Value = error;
                command.Parameters.Add("@ErrorCode", SqlDbType.VarChar);
                command.Parameters["@ErrorCode"].Value = errorCode;
                command.Parameters.Add("@ThoiGianNhan", SqlDbType.DateTime);
                command.Parameters["@ThoiGianNhan"].Value = DateTime.Now;

                try
                {
                    connection.Open();
                    Int32 rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
        }

        public static DataTable GetSensorByMa(string maSensor)
        {
            string commandText = "Select s.MaSensor, s.TenSensor,s.MaxValue, s.MinValue, s.SoLanQuaGioiHan,s.DonVi, p.MaDuAn, p.TenDuAn, u.Email, u.DienThoai from [dbo].[Sensor]  s " +
                                  "join [dbo].[ThietBi]  e on s.MaThietBi = e.MaThietBi "+
                                  "join [dbo].[DuAn]  p on e.MaDuAn= p.MaDuAn " +
                                  "join [dbo].[NguoiSuDung]  u on p.UserName= u.UserName " +
                                 "where  s.MaSensor = @maSensor and e.Status=@status";
            SqlDataReader reader;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@maSensor", SqlDbType.VarChar);
                command.Parameters["@maSensor"].Value = maSensor;
                command.Parameters.Add("@status", SqlDbType.VarChar);
                command.Parameters["@status"].Value = Util.SessionConst.EQUIPMENT_ACTIVE;
                try
                {
                    connection.Open();
                    DataTable dt = new DataTable();
                    reader = command.ExecuteReader();
                    dt.Load(reader);
                    return dt;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }
        public static DataTable GetGioiHanCanhBao(string maSensor,string type)
        {
            string commandText = "Select * from [dbo].[GioiHanCanhBao]  p " +
                                 "where  p.MaSensor = @maSensor and p.Type = @type  order by p.GiaTri desc";
            SqlDataReader reader;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@maSensor", SqlDbType.VarChar);
                command.Parameters["@maSensor"].Value = maSensor;
                command.Parameters.Add("@type", SqlDbType.VarChar);
                command.Parameters["@type"].Value = type;
                try
                {
                    connection.Open();
                    DataTable dt = new DataTable();
                    reader = command.ExecuteReader();
                    dt.Load(reader);
                    return dt;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }
        public static DataTable GetGioiHanCanhBaoByMa(string maSensor)
        {
            string commandText = "Select * from [dbo].[GioiHanCanhBao]  p " +
                                 "where  p.MaSensor = @maSensor order by p.GiaTri desc";
            SqlDataReader reader;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@maSensor", SqlDbType.VarChar);
                command.Parameters["@maSensor"].Value = maSensor;
                try
                {
                    connection.Open();
                    DataTable dt = new DataTable();
                    reader = command.ExecuteReader();
                    dt.Load(reader);
                    return dt;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }

        public static int GetCountGioiHanCanhbao(int id)
        {
            string commandText = "Select p.CountWarning from [dbo].[GioiHanCanhBao]  p " +
                                 "where  p.Id = @id";
            SqlDataReader reader;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@id", SqlDbType.Int);
                command.Parameters["@id"].Value = id;
                try
                {
                    connection.Open();
                    DataTable dt = new DataTable();
                    reader = command.ExecuteReader();
                    dt.Load(reader);
                    if(dt.Rows.Count>0)
                        return Convert.ToInt32(dt.Rows[0]["CountWarning"]);
                   
                }
                catch (Exception ex)
                {
                    return 0;
                }
            }
            return 0;
        }

        public static int UpdateGioiHanCanhbao(int id,string type)
        {
            string commandText = "";
            if (type == "")
                return 0;
            if (type == "0")
            {
                commandText =
                "Update [dbo].[GioiHanCanhBao]  set [CountWarning] = 0  where  [Id] = @id";
            }
            else
            {
                commandText =
                "Update [dbo].[GioiHanCanhBao]  set [CountWarning] =  ((select CountWarning from [dbo].[GioiHanCanhBao] where [Id] = @id) " +
             type + " ) where  [Id] = @id";
            
            }
            
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@id", SqlDbType.Int);
                command.Parameters["@id"].Value = id;

                try
                {
                    connection.Open();
                    Int32 rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
        }

        //public static DataTable GetUserWarning(string maSensor)
        //{
        //    string commandText = "Select p.MaSensor, p.MaThietBi, p.MaDuAn, p.UserName, u.Email, u.DienThoai from [dbo].[UserWarning]  p " +
        //                            "join [dbo].[NguoiSuDung] u on p.UserName = u.UserName " +
        //                        " where  p.MaSensor = @maSensor ";
        //    SqlDataReader reader;
        //    using (SqlConnection connection = new SqlConnection(connectionString))
        //    {
        //        SqlCommand command = new SqlCommand(commandText, connection);

        //        command.Parameters.Add("@maSensor", SqlDbType.VarChar);
        //        command.Parameters["@maSensor"].Value = maSensor;
        //        try
        //        {
        //            connection.Open();
        //            DataTable dt = new DataTable();
        //            reader = command.ExecuteReader();
        //            dt.Load(reader);
        //            return dt;
        //        }
        //        catch (Exception ex)
        //        {
        //            return null;
        //        }
        //    }
        //}
        public static DataTable GetUserWarning(int? maDuAn)
        {
            string commandText = "Select p.UserName, u.Email, u.DienThoai from [dbo].[PhanQuyenDuAn]  p " +
                                    "join [dbo].[NguoiSuDung] u on p.UserName = u.UserName " +
                                " where  p.MaDuAn = @maDuAn ";
            SqlDataReader reader;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@maDuAn", SqlDbType.Int);
                command.Parameters["@maDuAn"].Value = maDuAn;
                try
                {
                    connection.Open();
                    DataTable dt = new DataTable();
                    reader = command.ExecuteReader();
                    dt.Load(reader);
                    return dt;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }
        public static bool InsertNewData(EquipmentData eq)
        {
            bool isSend = false;
            try
            {
                MappingSensor(eq);
                if (eq == null) return false;
                var obj = (from e in eq.SensorDict.Values
                           where (e.DbId > 0 && (!string.IsNullOrEmpty(e.MaSensorDb)))
                    let v = DecodeSensorValue(e)
                    select new SensorValuesHistory
                    {
                        GiaTri = v, 
                        MaSensor = e.MaSensorDb, 
                        ThoiGianDo = e.LogTime, 
                        ThoiGianNhan = DateTime.Now, 
                        MaDuAn = e.MaDuAn
                    }).ToList();
                foreach (var sensorV in obj)
                {
                    if (!String.IsNullOrEmpty(sensorV.MaSensor))
                    {
                        isSend = false;
                        var dt = GetSensorByMa(sensorV.MaSensor);
                        if (dt != null && dt.Rows.Count > 0)
                        {
                            #region check maxvalue
                            if (!string.IsNullOrEmpty(dt.Rows[0]["MaxValue"].ToString()) && sensorV.GiaTri > Convert.ToDouble(dt.Rows[0]["MaxValue"].ToString()))
                            {
                                var dtCanhBao = GetGioiHanCanhBao(sensorV.MaSensor, SessionConst.MAX_TYPE);
                                if(dtCanhBao!=null&&dtCanhBao.Rows.Count>0)
                                    for (int i = 0; i < dtCanhBao.Rows.Count; i++)
                                    {
                                        var id = Convert.ToInt32(dtCanhBao.Rows[i]["Id"]);
                                        if (sensorV.GiaTri >= Convert.ToDouble(dtCanhBao.Rows[i]["GiaTri"]))
                                        {

                                            if (!isSend && Convert.ToDouble(dtCanhBao.Rows[i]["CountWarning"]) >= Convert.ToInt32(dt.Rows[0]["SoLanQuaGioiHan"]))
                                            {
                                                //Email1ƒEmail2€Tensenso€tenduan€giatridodc€maxvalue€
                                                string message = "1€";//vuot qua gia tri max
                                                var dtUser = GetUserWarning(sensorV.MaDuAn) ;
                                                
                                                //var list = new List<string>();
                                                if(dtUser.Rows.Count <=0)
                                                {
                                                    message += dt.Rows[0]["DienThoai"].ToString() + "€";
                                                    message += dt.Rows[0]["Email"].ToString() + "€";//list of mail
                                                }
                                                else
                                                {
                                                    var lEmail = "";
                                                    var lPhone = "";
                                                    for (int index = 0; index < dtUser.Rows.Count; index++)
                                                    {
                                                        lPhone += dtUser.Rows[index]["DienThoai"].ToString() + "ƒ";//list of Phone
                                                        lEmail += dtUser.Rows[index]["Email"].ToString() + "ƒ";//list of mail
                                                    }
                                                    message += lPhone + "€";
                                                    message += lEmail + "€";
                                                }
                                                message += dt.Rows[0]["TenSensor"].ToString() + "€";//ten sensor
                                                message += dt.Rows[0]["TenDuAn"].ToString() + "€";
                                                message += sensorV.GiaTri.ToString() + dt.Rows[0]["DonVi"] +"€";
                                                message += dt.Rows[0]["MaxValue"] + "€";
                                                //message = "Sensor: <b>" + dt.Rows[0]["TenSensor"].ToString() + "</b>, thuộc dự án: <b>" + dt.Rows[0]["TenDuAn"].ToString();
                                                //message += "</b>, Đo được giá trị quá giới hạn cho phép <br />";
                                                //message += " Giá trị đo được: <b>" + sensorV.GiaTri + "</b><br />";
                                                //message += " Giá trị tối đa cho phép: <b>" + dt.Rows[0]["MaxValue"] + "</b><br />";
                                                //SendEmail.SendingEmail(list, "thehegeo2014@gmail.com ", "TheHeGeo cảnh báo sensor đo được dữ liệu quá giới hạn cho phép!", message, "smtp.gmail.com", 587, "thehegeo2014", "12345@trewq");
                                                TcpSocketRequest sock = new TcpSocketRequest();
                                                if (sock.SendData(message) > 0)
                                                {
                                                    isSend = true;
                                                    //sau khi gửi mail cảnh báo reset biến count về 0
                                                    UpdateGioiHanCanhbao(id, "0");
                                                }
                                            }
                                            else
                                            {
                                                //Tăng biến count 1 đơn vị
                                                UpdateGioiHanCanhbao(id, "+ 1");//1 kiểu tăng biến count
                                            }
                                        }
                                        
                                    }
                            }
                            #endregion           
                            #region check min
                            else if (!string.IsNullOrEmpty(dt.Rows[0]["MinValue"].ToString()) && sensorV.GiaTri < Convert.ToDouble(dt.Rows[0]["MinValue"]))
                            {
                                var dtCanhBao = GetGioiHanCanhBao(sensorV.MaSensor, SessionConst.MIN_TYPE);
                                if (dtCanhBao != null && dtCanhBao.Rows.Count > 0)
                                    for (int i = dtCanhBao.Rows.Count-1; i >=0; i--)
                                    {
                                        var id = Convert.ToInt32(dtCanhBao.Rows[i]["Id"]);
                                        if (sensorV.GiaTri <= Convert.ToDouble(dtCanhBao.Rows[i]["GiaTri"]))
                                        {
                                            if (!isSend && Convert.ToDouble(dtCanhBao.Rows[i]["CountWarning"]) >= Convert.ToInt32(dt.Rows[0]["SoLanQuaGioiHan"]))
                                            {
                                                //Type€Phone€Email1ƒEmail2€Tensenso€tenduan€giatridodc€minvalue€
                                                string message = "0€";//nho hon gia tri min
                                                var dtUser = GetUserWarning(sensorV.MaDuAn);
                                                if (dtUser.Rows.Count <= 0)
                                                {
                                                    message += dt.Rows[0]["DienThoai"].ToString() + "€";
                                                    message += dt.Rows[0]["Email"].ToString() + "€";//list of mail
                                                }
                                                else
                                                {
                                                    var lEmail = "";
                                                    var lPhone = "";
                                                    for (int index = 0; index < dtUser.Rows.Count; index++)
                                                    {
                                                        lPhone += dtUser.Rows[index]["DienThoai"].ToString() + "ƒ";//list of Phone
                                                        lEmail += dtUser.Rows[index]["Email"].ToString() + "ƒ";//list of mail
                                                    }
                                                    message += lPhone + "€";
                                                    message += lEmail + "€";
                                                }
                                                message += dt.Rows[0]["TenSensor"].ToString() + "€";//ten sensor
                                                message += dt.Rows[0]["TenDuAn"].ToString() + "€";
                                                message += sensorV.GiaTri.ToString() + "€";
                                                message += dt.Rows[0]["MinValue"].ToString() + dt.Rows[0]["DonVi"] + "€";
                                               /* var list = new List<string> { dt.Rows[0]["Email"].ToString() };
                                                string message = "Sensor: <b>" + dt.Rows[0]["TenSensor"].ToString() + "</b>, thuộc dự án: <b>" + dt.Rows[0]["TenDuAn"].ToString();
                                                message += "</b>, Đo được giá trị nhỏ hơn giới hạn cho phép <br />";
                                                message += " Giá trị đo được: <b>" + sensorV.GiaTri + "</b><br />";
                                                message += " Giá trị tối thiểu cho phép: <b>" + dt.Rows[0]["MinValue"] + "</b><br />";
                                               // SendEmail.SendingEmail(list, "thehegeo2014@gmail.com ", "TheHeGeo cảnh báo sensor đo được dữ liệu quá giới hạn cho phép!", message, "smtp.gmail.com", 587, "thehegeo2014", "12345@trewq");*/
                                                TcpSocketRequest sock = new TcpSocketRequest();
                                                if (sock.SendData(message) > 0)
                                                {
                                                    isSend = true;
                                                    //sau khi gửi mail cảnh báo reset biến count về 0
                                                    UpdateGioiHanCanhbao(id, "0");
                                                }
                                             }
                                            else
                                            {
                                                //Tăng biến count 1 đơn vị
                                                UpdateGioiHanCanhbao(id, "+ 1");//1 kiểu tăng biến count
                                            }
                                        }
                                    }
                            }
                            #endregion
                            else
                            {
                                var dtCanhBao = GetGioiHanCanhBaoByMa(sensorV.MaSensor);
                                for (int i = 0; i < dtCanhBao.Rows.Count; i++)
                                {
                                    var id = Convert.ToInt32(dtCanhBao.Rows[i]["Id"]);
                                    var count = GetCountGioiHanCanhbao(id);
                                     if (count > 0)
                                                UpdateGioiHanCanhbao(id, "- 1");//-1 là kiểu giảm biến count
                                }
                                
                            }

                        }
                        InsertSensorValuesTest(sensorV.MaDuAn, sensorV.MaSensor, sensorV.GiaTri, sensorV.ThoiGianDo);
                        InsertSensorValuesHistory(sensorV.MaDuAn, sensorV.MaSensor, sensorV.GiaTri, sensorV.ThoiGianDo);
                        AddSensorValues1000(sensorV.MaDuAn, sensorV.MaSensor, sensorV.GiaTri, sensorV.ThoiGianDo);
                    }
                }

                var firstList = (from e in obj
                                 group e by e.MaSensor into g
                                 select g.OrderByDescending(e => e.ThoiGianDo).FirstOrDefault() into r

                                 select r).ToList();
                foreach (var sensor in firstList)
                {
                   
                   
                    var s = CheckSensorLatestValue(sensor.MaSensor);
                    if (s == false)
                    {
                        InsertSensorLatestValue(sensor.MaDuAn,sensor.MaSensor, sensor.GiaTri, sensor.ThoiGianDo);
                    }
                    else
                    {
                        UpdateSensorLatestValue(sensor.MaDuAn,sensor.MaSensor, sensor.GiaTri, sensor.ThoiGianDo);
                    }
                }

              
                return true;
            }
            catch (Exception ex)
            {
                if (eq != null)
                {
                    if(ex.InnerException==null)
                        eq.ErrStr = ex.Message + Environment.NewLine + ex.StackTrace;
                    else
                    {
                        eq.ErrStr = ex.InnerException.Message + Environment.NewLine + ex.InnerException.StackTrace;
                    }
                }
                return false;
            }
        }

        public static bool NeedInsert(EquipmentData eq)
        {
            return true;
        }

        public static double DecodeSensorValue(TheHeGeoDataProcess.Sensor sensor)
        {
            double rs;
            try
            {
                var lss = sensor.CongThuc;
                if (!string.IsNullOrEmpty(lss))
                {
                    var ct = lss.Replace("x", Math.Round(sensor.Value, 4).ToString());
                    rs = Math.Round(Convert.ToDouble(new DataTable().Compute(ct, string.Empty)), 4);
                }
                else
                {
                    rs = Math.Round(sensor.Value, 4);
                }
            }
            catch (Exception)
            {
                rs= sensor.Value;
            }
            return rs;
        }

        public static DataTable SelectSensorMappingNo(string maTbi)
        {
            string commandText = "Select s.MappingNo,s.MaSensor,p.MaDuAn,s.Id,s.CongThuc from [dbo].[ThietBi] p " +
                                 "join [dbo].[Sensor] s on p.MaThietBi = s.MaThietBi " +
                                 "where  p.MaThietBi = @maThietBi and p.Status=0 and s.TrangThaiTest=1";
            SqlDataReader reader;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);

                command.Parameters.Add("@maThietBi", SqlDbType.VarChar);
                command.Parameters["@maThietBi"].Value = maTbi;
                try
                {
                    connection.Open();
                    DataTable dt = new DataTable();
                    reader = command.ExecuteReader();
                    dt.Load(reader);
                    return dt;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }

        public static int UpdateSensors(string maSensor)
        {

            string commandText = "Update [dbo].[Sensor] set [DaNhanDuLieuTest] = @daNhanDuLieuTest,[LastUpdateTime] = @lastUpdateTime where  [MaSensor] = @maSensor";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);
                command.Parameters.Add("@maSensor", SqlDbType.VarChar);
                command.Parameters["@maSensor"].Value = maSensor;
                command.Parameters.Add("@lastUpdateTime", SqlDbType.DateTime);
                command.Parameters["@lastUpdateTime"].Value = DateTime.Now;
                command.Parameters.Add("@daNhanDuLieuTest", SqlDbType.Bit);
                command.Parameters["@daNhanDuLieuTest"].Value = 1;
                try
                {
                    connection.Open();
                    Int32 rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
        }
        public static void MappingSensor(EquipmentData eq)
        {
            try
            {
               var dt = SelectSensorMappingNo(eq.DeviceId);
                if (dt == null || dt.Rows.Count == 0)
                    return;
                foreach (var s in eq.SensorDict.Values)
                {
                    var dr = dt.NewRow();                    
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if(dt.Rows[i]["MappingNo"].ToString().Equals(s.Id.Split('_')[1]))
                        {
                            dr = dt.Rows[i];
                            break;
                        }
                    }
                    if (dr["MaSensor"] == null || String.IsNullOrEmpty(dr["MaSensor"].ToString()))
                        s.MaSensorDb = "";
                    else
                    {
                        s.MaSensorDb = dr["MaSensor"].ToString();
                    }
                    if (dr["Id"] == null || string.IsNullOrEmpty(dr["Id"].ToString()))
                        s.DbId = -1;
                    else
                        s.DbId = Convert.ToInt32(dr["Id"]);
                    if (dr["MaDuAn"] == null || string.IsNullOrEmpty(dr["MaDuAn"].ToString()))
                        s.MaDuAn = -1;
                    else
                        s.MaDuAn = Convert.ToInt32(dr["MaDuAn"]);
                    if (dr["CongThuc"] == null || string.IsNullOrEmpty(dr["CongThuc"].ToString()))
                        s.CongThuc = string.Empty;
                    else
                        s.CongThuc = dr["CongThuc"].ToString();
                    //s.MaSensorDb = !String.IsNullOrEmpty(dr["MaSensor"].ToString()) ? dr["MaSensor"].ToString() : "";
                    //s.DbId = !String.IsNullOrEmpty(dr["Id"].ToString()) ? Convert.ToInt32(dr["Id"]) : -1;
                   // s.MaDuAn = !String.IsNullOrEmpty(dr["MaDuAn"].ToString()) ? Convert.ToInt32(dr["MaDuAn"]) : -1;
                    //s.CongThuc = !String.IsNullOrEmpty(dr["CongThuc"].ToString()) ? dr["CongThuc"].ToString() : string.Empty;
                    //if (!String.IsNullOrEmpty(dr["MaSensor"].ToString()))
                    //    UpdateSensors(dr["MaSensor"].ToString());
                    if(!String.IsNullOrEmpty(s.MaSensorDb))
                        UpdateSensors(s.MaSensorDb);
                    
                }

            }
            catch (Exception)
            {
                eq = null;
            }
        }

        public static void InsertRaw(EquipmentData eq)
        {
           
            InsertRawData(eq.DeviceId, eq.RawData, eq.ErrStr, eq.ErrCode);
        }
        public static void InsertRaw(string err,string errorCode)
        {
            InsertRawData("", "", err, errorCode);
        }
    }
}